슬로 쿼리 로그에서 개선 후보 찾기

슬로 쿼리 로그에서 개선 후보 찾기

한눈에 보기

가장 느린 쿼리 한 건이 반드시 가장 먼저 고칠 쿼리는 아니다. 5초짜리 쿼리가 하루 한 번 실행되는 동안 80ms 쿼리가 초당 500번 호출되면 후자가 훨씬 많은 DB 시간을 소비한다. 슬로 로그를 query digest로 묶고 총 실행 시간, 호출 횟수, 평균·최대·분포, 검사 행 수, 반환 행 수와 잠금 시간을 함께 비교해야 한다.

목차

문제가 되는 상황

장애가 발생한 날 슬로 로그에서 가장 오래 걸린 SQL을 찾아 보니 8.2초짜리 관리자 보고서 쿼리가 있었다고 하자. 곧바로 이 쿼리에 인덱스를 추가했지만 CPU와 응답 지연은 거의 줄지 않았다.

같은 시간대에 95ms짜리 상품 목록 쿼리가 120만 번 실행되고 있었기 때문이다.

Query A: 8.2s × 3회        = 24.6 DB seconds
Query B: 0.095s × 1,200,000회 = 114,000 DB seconds

Query A는 한 번만 보면 훨씬 느리다. 하지만 시스템이 소비한 총 시간과 동시 부하에는 Query B가 압도적인 영향을 준다. 반대로 Query A가 결제 마감 같은 중요한 동기 요청이라면 낮은 빈도라도 사용자 영향 때문에 우선할 수 있다.

슬로 쿼리 분석은 “가장 긴 Query_time 정렬”이 아니라 호출량, 사용자 경로, 자원 소비를 결합해 개선 순서를 정하는 작업이다.

슬로 쿼리 로그가 수집하는 범위

MySQL의 슬로 쿼리 로그는 기본적으로 long_query_time보다 오래 걸리고 min_examined_row_limit 조건을 충족한 문장을 기록한다. long_query_time은 CPU 시간만이 아니라 실제 경과 시간 기준이다. 따라서 같은 SQL도 부하와 잠금 대기에 따라 어떤 때는 기록되고 어떤 때는 빠질 수 있다.

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'min_examined_row_limit';
SHOW VARIABLES LIKE 'log_output';

설정 개념은 다음과 같다. 관리형 DB에서는 파라미터 그룹과 제공되는 로그 기능을 사용해야 할 수 있다.

slow_query_log = ON
long_query_time = 0.5
min_examined_row_limit = 100

공식 문서 기준으로 기록 여부에는 관리자 문장 포함 설정, long_query_time 또는 인덱스 미사용 기록 설정, 최소 검사 행 수, throttle 등이 순서대로 영향을 준다. 세부 설정은 MySQL Slow Query Log 문서와 사용 버전의 시스템 변수 문서를 확인해야 한다.

로그가 없다는 것은 느린 쿼리가 없다는 뜻이 아니다

임계값보다 1ms 빠른 쿼리, 최소 검사 행보다 적은 쿼리, 로깅 대상에서 제외된 관리자·복제 문장은 보이지 않을 수 있다. 로그 설정이 관측 가능한 세계의 경계를 만든다.

임계값 하나가 만드는 사각지대

long_query_time = 1s라면 950ms 쿼리는 아무리 자주 실행되어도 슬로 로그에 남지 않는다.

threshold: 1000ms

query X: 1100ms × 10회     → 기록됨
query Y:  950ms × 100,000회 → 기록되지 않음

운영 초기에는 너무 낮은 임계값이 로그 I/O와 저장량을 폭증시킬 수 있어 보수적으로 시작할 수 있다. 반대로 문제 분석 기간에는 제한된 시간 동안 임계값을 낮추고 수집량을 관찰하는 방식이 필요하다.

슬로 로그와 함께 Performance Schema의 statement summary를 보면 임계값 아래의 반복 쿼리도 digest 단위로 파악할 수 있다.

SELECT digest_text,
       count_star,
       sum_timer_wait,
       avg_timer_wait,
       max_timer_wait,
       sum_rows_examined,
       sum_rows_sent
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name = 'app'
ORDER BY sum_timer_wait DESC
LIMIT 20;

timer 단위와 컬럼 지원 범위는 MySQL 버전에 맞게 변환해야 한다. Performance Schema 수집이 비활성화된 항목도 있을 수 있다.

파라미터가 다른 SQL을 digest로 묶는다

다음 SQL은 값만 다르고 구조는 같다.

SELECT id, status
FROM orders
WHERE user_id = 101
  AND created_at >= '2026-08-01';

SELECT id, status
FROM orders
WHERE user_id = 8421
  AND created_at >= '2026-09-01';

각 원문을 별개로 세면 같은 문제를 여러 줄로 나눠 보게 된다. query digest 또는 fingerprint는 리터럴을 자리표시자로 정규화해 같은 쿼리 형태로 묶는다.

SELECT id, status
FROM orders
WHERE user_id = ?
  AND created_at >= ?;

flowchart LR
    A[user_id=101 SQL] --> D[하나의 query digest]
    B[user_id=8421 SQL] --> D
    C[user_id=500 SQL] --> D
    D --> E[호출 수·총 시간·행 수 집계]

digest별로 묶어야 다음 질문에 답할 수 있다.

다만 같은 digest 안에서도 파라미터에 따른 데이터 분포 차이가 크다. 평균적인 user는 주문 20건이지만 특정 user는 200만 건일 수 있다. digest 집계 후에는 느린 샘플의 파라미터 특성을 민감 정보 없이 분류해야 한다.

가장 느린 한 번보다 총 DB 시간을 본다

우선순위를 위한 첫 번째 축은 전체 기간의 누적 비용이다.

total DB time ≈ execution count × average latency

가상의 digest 집계:

Digest 호출 수 평균 최대 총 시간
주문 목록 1,200,000 95ms 1.8s 31.7시간
월간 보고서 3 8.2s 9.1s 24.6초
재고 확인 8,000,000 8ms 700ms 17.8시간
검색 자동완성 2,500,000 18ms 1.1s 12.5시간

주문 목록은 평균 100ms도 안 되지만 총 DB 시간을 가장 많이 소비한다. 30%만 개선해도 많은 CPU와 커넥션 점유 시간을 돌려받을 수 있다.

하지만 합계만으로 순위를 확정하지 않는다. 다음 항목을 가중한다.

평균만 보면 꼬리 지연을 놓친다

평균 20ms인 쿼리도 일부 요청이 5초 걸릴 수 있다.

99,000회 × 10ms
1,000회  × 1,000ms

평균 ≈ 20ms

평균만 보면 건강해 보이지만 1%의 사용자는 1초를 경험한다. p95, p99 같은 percentile이나 latency histogram을 함께 봐야 한다. MySQL 8.4 Performance Schema에는 statement latency histogram summary 테이블이 있어 digest별 분포 분석에 활용할 수 있다. 기능 활성화와 보존 범위는 환경별로 확인한다.

꼬리 지연의 원인은 쿼리 계획만이 아닐 수 있다.

슬로 로그의 한 샘플과 애플리케이션 trace, DB wait 지표를 같은 request ID와 시각으로 연결하면 구분하기 쉽다.

Rows examined와 rows sent의 차이를 본다

결과 20건을 보내기 위해 100만 행을 검사하는 쿼리는 개선 가능성이 높다.

Rows_examined: 1,000,000
Rows_sent:             20
ratio:             50,000 : 1

간단한 낭비 비율을 만들 수 있다.

examined per sent = rows_examined / max(rows_sent, 1)
형태 Rows examined Rows sent 해석
기본 키 단건 1 1 좁은 접근
최신 목록 20 20 정렬 인덱스와 조기 종료 가능성
필터 후 목록 50,000 20 인덱스 접두·필터 순서 점검
전체 집계 10,000,000 1 업무상 전체 읽기가 필요할 수 있음

비율이 높다고 무조건 문제는 아니다. 정확한 전체 COUNT, 보고서 집계는 많은 행을 읽는 것이 업무 자체일 수 있다. 이 경우 인덱스보다 사전 집계, read model, 캐시, 배치 주기 변경을 검토한다.

반대로 Rows_examined가 작아도 느릴 수 있다. 큰 BLOB 전송, 잠금 대기, 함수 계산, 네트워크와 클라이언트 소비 속도가 원인일 수 있다.

Query time과 lock time을 구분한다

슬로 로그 샘플에는 전체 query time과 lock time 같은 정보가 포함될 수 있다.

Query_time: 2.400000
Lock_time:  2.300000
Rows_sent: 1
Rows_examined: 1

한 행만 읽었는데 2.4초 걸렸고 대부분이 잠금 대기라면 인덱스를 추가해도 해결되지 않는다. 누가 잠금을 오래 보유했는지, 트랜잭션 범위와 외부 I/O를 조사해야 한다.

Query time ≈ lock wait + CPU + page I/O + execution + result handling

슬로 로그의 lock time이 모든 종류의 대기를 완전하게 분리해 주는 것은 아니다. Performance Schema wait event, InnoDB transaction·lock 정보, 애플리케이션 trace와 함께 분석한다.

느린 피해자와 원인 제공자를 구분한다

로그에 남은 SELECT 또는 UPDATE는 오래 기다린 피해자일 수 있다. 실제 원인은 먼저 시작해 잠금을 잡은 채 외부 API를 호출한 다른 트랜잭션일 수 있다.

개선 후보를 고르는 우선순위 표

후보를 감으로 고르지 않도록 digest별 요약표를 만든다.

항목 의미 우선순위를 높이는 신호
total latency 기간 전체 DB 점유 시간 전체 상위 비중
execution count 호출 빈도 초당 호출이 매우 높음
p95/p99 또는 histogram 꼬리 지연 사용자 timeout과 연결
rows examined/sent 읽기 효율 작은 결과에 비해 지나치게 큼
lock time 경합 실행보다 대기가 대부분
errors 실패 비용 timeout·deadlock 반복
business path 사용자 영향 결제·로그인·핵심 목록
optimization leverage 개선 여지 불필요 호출, N+1, 명확한 인덱스 누락

정확한 하나의 점수로 모든 상황을 표현하기는 어렵지만 1차 정렬에는 가중 점수를 사용할 수 있다.

priority score =
  normalized(total_latency) × 0.35
+ normalized(p99_latency)   × 0.20
+ normalized(call_count)    × 0.15
+ normalized(rows_waste)    × 0.15
+ business_criticality      × 0.15

이는 수학적 진리가 아니라 팀이 빠뜨리는 축을 줄이는 체크리스트다. 장애 상황에서는 오류와 timeout을, 비용 최적화에서는 총 CPU·I/O를 더 크게 가중할 수 있다.

로그 수집을 안전하게 운영하는 방법

짧은 기간에 단계적으로 임계값을 낮춘다

평상시: long_query_time = 1s
진단 1단계: 500ms, 30분
진단 2단계: 100ms, 샘플링·용량 감시

환경에 따라 runtime 변경 범위와 신규 세션 적용 여부를 확인한다. 수집 전후 로그 쓰기량과 디스크 여유, 관측 시스템 비용을 본다.

원문 파라미터와 개인정보를 보호한다

SQL에는 이메일, 검색어, 토큰, 메모가 리터럴로 포함될 수 있다. 애플리케이션은 바인딩 파라미터를 사용하고, 외부 분석 도구로 전송하기 전에 마스킹·접근 통제·보존 기간을 적용한다.

나쁜 공유:
WHERE email = 'real-user@example.com'

안전한 digest:
WHERE email = ?

로그 순환과 보존을 계획한다

로그 파일이 디스크를 채우지 않도록 rotation, 압축, 보존 기간과 중앙 수집 실패 시 동작을 정한다. 관리형 DB라면 로그 export 비용과 지연도 확인한다.

인덱스 미사용 쿼리를 무제한 기록하지 않는다

작은 테이블 전체 스캔까지 모두 기록하면 로그가 폭증할 수 있다. MySQL은 관련 기록을 throttle하는 설정을 제공한다. “인덱스 미사용” 자체보다 검사 행 수와 실행 시간을 함께 필터링한다.

슬로 로그에서 실행 계획 분석까지

후보 digest를 고른 뒤 다음 흐름으로 진단한다.

flowchart TD
    A[슬로 로그·Performance Schema 수집] --> B[Digest별 집계]
    B --> C[총 시간·p99·rows 기준 후보 선택]
    C --> D[대표 파라미터와 데이터 분포 확인]
    D --> E[EXPLAIN]
    E --> F[안전한 환경의 EXPLAIN ANALYZE]
    F --> G[인덱스·쿼리·호출 구조 개선]
    G --> H[동일 트래픽에서 전후 비교]

대표 파라미터는 평균값 하나가 아니다.

일반 EXPLAIN으로 위험한 전체 스캔과 예상 행을 확인한 뒤, 재현 데이터에서 EXPLAIN ANALYZE로 actual rows와 loops를 본다. 읽는 순서는 EXPLAIN과 EXPLAIN ANALYZE 읽는 순서에 정리했다.

개선 수단은 인덱스만이 아니다.

호출 제거/캐시
N+1을 batch query로 통합
반환 열 축소
기간·페이지 크기 제한
복합 인덱스 조정
사전 집계/read model
비동기 배치로 이동
트랜잭션 범위 축소

가장 싼 쿼리는 실행하지 않는 쿼리다. 동일 데이터를 요청 안에서 여러 번 조회하거나 클라이언트 polling이 과도하다면 SQL 미세 최적화보다 호출 구조를 바꾸는 편이 효과가 크다.

개선 전후를 같은 시간대에서 검증한다

월요일 새벽과 금요일 피크 시간을 비교하면 트래픽 차이를 쿼리 개선으로 오해할 수 있다. 배포 전후 같은 길이와 유사한 요청량의 기간을 비교한다.

지표 변경 전 변경 후 함께 볼 보정값
digest 호출 수 1.2M 1.3M 전체 요청 수
평균 latency 95ms 24ms cache hit ratio
p99 latency 1.8s 180ms 동시 접속 수
총 DB 시간 31.7h 8.7h 수집 기간
rows examined/call 12,000 25 데이터 증가량
INSERT p95 4.0ms 4.5ms 새 인덱스 쓰기 비용

인덱스를 추가했다면 핵심 SELECT뿐 아니라 INSERT·UPDATE, 인덱스 크기, replica lag도 확인한다. 성능 개선이 실제 사용자 API의 p95·p99와 timeout 감소로 이어졌는지도 애플리케이션 지표에서 검증한다.

자주 생기는 잘못된 판단

가장 긴 쿼리 한 건부터 고친다

낮은 빈도의 관리자 쿼리보다 자주 호출되는 80ms 쿼리가 총 부하를 더 많이 만들 수 있다.

평균만 본다

특정 파라미터와 잠금 경합에서만 느린 꼬리 요청을 놓친다. histogram과 샘플 trace를 본다.

Rows examined가 높으면 무조건 인덱스를 추가한다

전체 집계에는 많은 읽기가 본질적일 수 있다. 사전 집계와 read model이 더 적합할 수 있다.

인덱스 미사용을 모두 문제로 본다

작은 코드 테이블과 테이블 대부분을 반환하는 쿼리는 전체 스캔이 합리적일 수 있다.

슬로 로그에 없으면 정상이라고 본다

임계값 아래의 고빈도 쿼리와 커넥션 풀 대기는 보이지 않을 수 있다. statement summary와 애플리케이션 trace가 필요하다.

SQL 원문을 그대로 공유한다

민감한 리터럴과 내부 스키마가 유출될 수 있다. digest와 재구성한 예제로 분석한다.

결론

슬로 쿼리 로그의 목적은 가장 느린 SQL 한 줄을 찾는 것이 아니라 제한된 DB 자원을 가장 많이 소비하고 사용자 경험에 영향을 주는 쿼리 형태를 찾는 것이다. 원문을 query digest로 묶고 호출 횟수, 총 실행 시간, 평균과 꼬리 지연, rows examined/sent, lock time을 함께 비교해야 한다.

로그 임계값은 관측 범위를 결정하므로 Performance Schema statement summary와 histogram으로 사각지대를 보완한다. 후보를 고른 뒤에는 큰 데이터와 작은 데이터 파라미터를 나눠 실행 계획을 분석하고, 인덱스뿐 아니라 호출 제거·배치·사전 집계까지 해결책을 열어 둔다. 개선 결과는 같은 트래픽 조건에서 digest 지표, API latency와 쓰기 비용을 함께 비교해 검증해야 한다.

관련 노트

참고 자료